#!/usr/bin/env python
# coding=utf-8

import MySQLdb;

class CrawlerDB: 
    def __init__(self):
        # 打开数据库连接
        self.db = MySQLdb.connect("localhost","app","firstapp","crawler");
    
    def savePage(self, page):
        # 使用cursor()方法获取操作游标 
        cursor = self.db.cursor();
        sql = "insert into pages(code, name, url) values(%d, '%s', '%s')" % (page.code, page.name, page.url);
        # 使用execute方法执行SQL语句
        cursor.execute(sql);

        if cursor.rowcount != 1:
            # @temp by peter: 
            print("这是错误的，需要异常处理");

        self.db.commit();
        cursor.close();
        return True;

    def getPageId(self, code):
        cursor = self.db.cursor();
        sql = "select id from pages where code = %d" % code;
        cursor.execute(sql);
        # 使用 fetchone() 方法获取一条数据库。
        data = cursor.fetchall();

        if len(data) != 1:
            # @temp by peter: 
            print("这是错误的，需要异常处理");

        result = data[0][0];
        cursor.close();
        return result;

    def saveImage(self, image):
        cursor = self.db.cursor();
        sql = "insert into images(name, page_id, url) values('%s', %d, '%s')" % (image.name, image.pageId, image.url);
        cursor.execute(sql);

        if cursor.rowcount != 1:
            # @temp by peter: 
            print("这是错误的，需要异常处理(%d, %s)" % (image.pageId, image.name));

        self.db.commit();
        cursor.close();
        return True;

    def getUndownImagesInPage(self, pageId):
        cursor = self.db.cursor();
        sql = "select id, name, page_id, url, hasDownload from images where page_id = %d and hasDownload = 0" % (pageId);
        cursor.execute(sql);
        data = cursor.fetchall();
        cursor.close();

        if len(data) <= 0:
            print("此ID: %d 下没有未下载的图片。" % (pageId));
            return None;

        return data;

    def getImagesInPage(self, pageId):
        cursor = self.db.cursor();
        sql = "select id, name, page_id, url, hasDownload from images where page_id = %d" % (pageId);
        cursor.execute(sql);
        data = cursor.fetchall();
        cursor.close();

        if len(data) <= 0:
            print("这是错误的，你的ID: %d 太大了，找不到数据。" % (pageId));
            return None;

        return data;

    def savePagePath(self, id, path):
        cursor = self.db.cursor();
        sql = "update pages set path = '%s' where id = %d" % (path, id);
        cursor.execute(sql);

        if cursor.rowcount != 1:
            # @temp by peter: 
            print("一条数据都没有修改，是不是错误自己判断: id:%d" % (id));
            cursor.close();
            return False;

        self.db.commit();
        cursor.close();
        return True;

    def flagImage(self, id):
        cursor = self.db.cursor();
        sql = "update images set hasDownload = 1 where id = %d" % (id);
        cursor.execute(sql);

        if cursor.rowcount != 1:
            # @temp by peter: 
            print("一条数据都没有修改，是不是错误自己判断: id:%d" % (id));

        self.db.commit();
        cursor.close();
        return True;

    def close(self):
        # 关闭数据库连接
        self.db.close();
